----------
-- tags --
----------






create table tags (
  tag_id bigserial primary key,
  chinese_display text,
  english_display text,
  parent_id bigint references tags on update cascade on delete restrict,
  type integer not null,
  image text,
	image_two text,
  description text,
  status integer,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index tags_parent_id on tags using BTREE (parent_id);
create index tags_status on tags using BTREE (status);
create index tags_type on tags using BTREE (type);

create table tags_history (
  change_sequence bigserial primary key,
  tag_id bigint,
  chinese_display text,
  english_display text,
  parent_id bigint,
  type integer,
  image text,
	image_two text,
  description text,
  status integer,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index tags_history_tag_id on tags_history using BTREE (tag_id);

create or replace function archive_tags() returns trigger as $archive_tags$
begin
	if (new.version = old.version + 1) then
		insert into tags_history (
	    tag_id,
	    chinese_display,
	    english_display,
	    parent_id,
	    type,
	    image,
	    description,
	    status,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
			old.tag_id,
	    old.chinese_display,
	    old.english_display,
	    old.parent_id,
	    old.type,
	    old.image,
	    old.description,
	    old.status,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_tags$ language plpgsql;
create trigger archive_tags before update on tags for each row execute procedure archive_tags();

-----------
-- users --
-----------
create table users (
  user_id bigserial primary key,
  type integer not null,
  password text not null,
  nickname text not null,
  sex integer,
  head_img text,
  birthday date,

  visible boolean,
  description text,
  creation_time timestamp,
  status integer not null,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index users_type on users using BTREE (type);
create index users_status on users using BTREE (status);

create table users_history (
  change_sequence bigserial primary key,
  user_id bigint,
  type integer,
  password text,
  nickname text,
  sex integer,
  head_img text,
  birthday date,
  visible boolean,
  description text,
  creation_time timestamp,
  status integer,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index users_history_user_id on users_history using BTREE (user_id);

create or replace function archive_users() returns trigger as $archive_users$
begin
	if (new.version = old.version + 1) then
		insert into users_history (
			user_id,
			type,
			password,
			nickname,
			sex,
			head_img,
			birthday,
			visible,
			description,
			creation_time,
			status,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.user_id,
			old.type,
			old.password,
			old.nickname,
			old.sex,
			old.head_img,
			old.birthday,
			old.visible,
			old.description,
			old.creation_time,
			old.status,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_users$ language plpgsql;
create trigger archive_users before update on users for each row execute procedure archive_users();

------------------
-- user_aliases --
------------------
create table user_aliases (
  user_id bigint references users on update cascade on delete restrict,
  type integer not null,
  alias_name text not null unique,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer,
  primary key (user_id, type)
);
create index user_aliases_alias_upper_username on user_aliases using BTREE (upper(alias_name));

create table user_aliases_history (
  change_sequence bigserial primary key,
  user_id bigint,
  type integer,
  alias_name text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index user_aliases_history_user_id on user_aliases_history using BTREE (user_id);

create or replace function archive_user_aliases() returns trigger as $archive_user_aliases$
begin
	if (new.version = old.version + 1) then
		insert into user_aliases_history (
			user_id,
			type,
			alias_name,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.user_id,
			old.type,
			old.alias_name,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_user_aliases$ language plpgsql;
create trigger archive_user_aliases before update on user_aliases for each row execute procedure archive_user_aliases();

-----------------
-- friendships --
-----------------
create table friendships (
  from_user_id bigint references users on update cascade on delete restrict,
  to_user_id bigint references users on update cascade on delete restrict,
  remark text,
  status integer not null,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer,
  primary key (from_user_id, to_user_id)
);
create index friendships_status on friendships using BTREE (status);

create table friendships_history (
  change_sequence bigserial primary key,
  from_user_id bigint,
  to_user_id bigint,
  remark text,
  status integer,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_friendships() returns trigger as $archive_friendships$
begin
	if (new.version = old.version + 1) then
		insert into friendships_history (
	    from_user_id,
	    to_user_id,
	    remark,
	    status,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.from_user_id,
	    old.to_user_id,
	    old.remark,
	    old.status,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_friendships$ language plpgsql;
create trigger archive_friendships before update on friendships for each row execute procedure archive_friendships();

-----------------
-- commercials --
-----------------
create table commercials (
  commercial_id bigserial primary key,
  name text,
  subname text,
  owner_user_id bigint references users on update cascade on delete restrict,
  address text,
  phone text,
  email text,
  cover_thumbnail text,
  cover text,
  register_time date,
  register_money bigint,
  contact_name text,
  description text,
	words text,
	label text,
  operating_time text,
  position_longitude float,
  position_latitude float,
  status integer not null,
	parking text,
	consumption_per_person  text,
	tips text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index commercials_status on commercials using BTREE (status);

create table commercials_history (
  change_sequence bigserial primary key,
  commercial_id bigserial,
  name text,
  subname text,
  owner_user_id bigint,
  address text,
  phone text,
  email text,
  cover_thumbnail text,
  cover text,
  register_time date,
  register_money bigint,
  contact_name text,
  description text,
	words text,
	label text,
  operating_time text,
  position_longitude float,
  position_latitude float,
  status integer,
	parking text,
	consumption_per_person  text,
	tips text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_commercials() returns trigger as $archive_commercials$
begin
	if (new.version = old.version + 1) then
		insert into commercials_history (
	    commercial_id,
	    name,
	    subname,
	    owner_user_id,
	    address,
	    phone,
	    email,
	    cover_thumbnail,
	    cover,
	    register_time,
	    register_money,
	    contact_name,
	    description,
      words,
      label,
	    operating_time,
	    position_longitude,
	    position_latitude,
	    status,
      parking ,
    	consumption_per_person,
    	tips,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.commercial_id,
	    old.name,
	    old.subname,
	    old.owner_user_id,
	    old.address,
	    old.phone,
	    old.email,
	    old.cover_thumbnail,
	    old.cover,
	    old.register_time,
	    old.register_money,
	    old.contact_name,
	    old.description,
      old.words ,
      old.label ,
	    old.operating_time,
	    old.position_longitude,
	    old.position_latitude,
	    old.status,
      old.parking ,
      old.consumption_per_person,
      old.tips,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercials$ language plpgsql;
create trigger archive_commercials before update on commercials for each row execute procedure archive_commercials();

---------------------
-- commercial_tags --
---------------------
create table commercial_tags (
  commercial_id bigint references commercials on update cascade on delete restrict,
  tag_id bigint references tags on update cascade on delete restrict,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer,
  primary key (commercial_id, tag_id)
);

create table commercial_tags_history (
  change_sequence bigserial primary key,
  commercial_id bigint,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer,
  tag_id bigint
);

create or replace function archive_commercial_tags() returns trigger as $archive_commercial_tags$
begin
	if (new.version = old.version + 1) then
		insert into commercial_tags_history (
	    commercial_id,
	    tag_id,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.commercial_id,
	    old.tag_id,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercial_tags$ language plpgsql;
create trigger archive_commercial_tags before update on commercial_tags for each row execute procedure archive_commercial_tags();

-----------------------
-- commercial_albums --
-----------------------
create table commercial_albums (
  album_id bigserial primary key,
  commercial_id bigint references commercials on update cascade on delete restrict,
  original_photo text,
  compressed_photo text,
  remark text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index commercial_albums_commercial_id on commercial_albums using BTREE (commercial_id);

create table commercial_albums_history (
  change_sequence bigserial primary key,
  album_id bigserial,
  commercial_id bigint,
  original_photo text,
  compressed_photo text,
  remark text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_commercial_albums() returns trigger as $archive_commercial_albums$
begin
	if (new.version = old.version + 1) then
		insert into commercial_albums_history (
	    album_id,
	    commercial_id,
	    original_photo,
	    compressed_photo,
	    remark,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.album_id,
	    old.commercial_id,
	    old.original_photo,
	    old.compressed_photo,
	    old.remark,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercial_albums$ language plpgsql;
create trigger archive_commercial_albums before update on commercial_albums for each row execute procedure archive_commercial_albums();

--------------------------------
-- commercial_recommendations --
--------------------------------
create table commercial_recommendations (
  recommendation_id bigserial primary key,
  commercial_id bigint references commercials on update cascade on delete restrict,
  user_id bigint references users on update cascade on delete restrict,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index commercial_recommendations_commercial_id on commercial_recommendations using BTREE (commercial_id);
create index commercial_recommendations_user_id on commercial_recommendations using BTREE (user_id);

create table commercial_recommendations_history (
  change_sequence bigserial primary key,
  recommendation_id bigserial,
  commercial_id bigint,
  user_id bigint,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_commercial_recommendations() returns trigger as $archive_commercial_recommendations$
begin
	if (new.version = old.version + 1) then
		insert into commercial_recommendations_history (
	    recommendation_id,
	    commercial_id,
	    user_id,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.recommendation_id,
	    old.commercial_id,
	    old.user_id,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercial_recommendations$ language plpgsql;
create trigger archive_commercial_recommendations before update on commercial_recommendations for each row execute procedure archive_commercial_recommendations();

---------------------------
-- commercial_activities --
---------------------------
create table commercial_activities (
  activity_id bigserial primary key,
  commercial_id bigint references commercials on update cascade on delete restrict,
  recommendation_id bigint references commercial_recommendations on update cascade on delete restrict,
  user_id bigint references users on update cascade on delete restrict,
  activity_time timestamp,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index commercial_activities_commercial_id on commercial_activities using BTREE (commercial_id);
create index commercial_activities_recommendation_id on commercial_activities using BTREE (recommendation_id);
create index commercial_activities_user_id on commercial_activities using BTREE (user_id);

create table commercial_activities_history (
  change_sequence bigserial primary key,
  activity_id bigserial,
  commercial_id bigint,
  recommendation_id bigint,
  user_id bigint,
  activity_time timestamp,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_commercial_activities() returns trigger as $archive_commercial_activities$
begin
	if (new.version = old.version + 1) then
		insert into commercial_activities_history (
	    activity_id,
	    commercial_id,
	    recommendation_id,
	    user_id,
	    activity_time,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.activity_id,
	    old.commercial_id,
	    old.recommendation_id,
	    old.user_id,
	    old.activity_time,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercial_activities$ language plpgsql;
create trigger archive_commercial_activities before update on commercial_activities for each row execute procedure archive_commercial_activities();

---------------
-- questions --
---------------
create table questions (
  question_id bigserial primary key,
  title text,
  tag_id bigint references tags on update cascade on delete restrict,
  expiry date,
  experience_points integer,
  option_a text,
  option_b text,
  solution_is_a boolean,
  image text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);
create index questions_tag_id on questions using BTREE (tag_id);
create index questions_expiry on questions using btree (expiry);

create table questions_history (
  change_sequence bigserial primary key,
  question_id bigserial,
  title text,
  tag_id bigint,
  expiry date,
  experience_points integer,
  option_a text,
  option_b text,
  solution_is_a boolean,
  image text,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_questions() returns trigger as $archive_questions$
begin
	if (new.version = old.version + 1) then
		insert into questions_history (
	    question_id,
	    title,
	    tag_id,
	    expiry,
	    experience_points,
	    option_a,
	    option_b,
	    solution_is_a,
	    image,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.question_id,
	    old.title,
	    old.tag_id,
	    old.expiry,
	    old.experience_points,
	    old.option_a,
	    old.option_b,
	    old.solution_is_a,
	    old.image,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_questions$ language plpgsql;
create trigger archive_questions before update on questions for each row execute procedure archive_questions();

-------------
-- answers --
-------------
create table answers (
  question_id bigint references questions on update cascade on delete restrict,
  user_id bigint references users on update cascade on delete restrict,
  a_is_selected boolean,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer,
  primary key (question_id, user_id)
);
create index answers_question_id on answers using BTREE (question_id);
create index answers_user_id on answers using BTREE (user_id);

create table answers_history (
  change_sequence bigserial primary key,
  question_id bigint,
  user_id bigint,
  a_is_selected boolean,
  proc_name text,
  proc_channel text,
  proc_ts timestamp,
  proc_user_id bigint,
  session_id text,
  version integer
);

create or replace function archive_answers() returns trigger as $archive_answers$
begin
	if (new.version = old.version + 1) then
		insert into answers_history (
	    question_id,
	    user_id,
	    a_is_selected,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.question_id,
	    old.user_id,
	    old.a_is_selected,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_answers$ language plpgsql;
create trigger archive_answers before update on answers for each row execute procedure archive_answers();

---------------
-- knowledge --
---------------
create table knowledge (
	knowledge_id bigserial primary key,
	user_id bigint references users on update cascade on delete restrict,
	picture text,
	title text,
	subtitle text,
	description text,
	createtime timestamp,
	status integer not null,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index knowledge_user_id on knowledge using BTREE (user_id);

create table knowledge_history (
	change_sequence bigserial primary key,
	knowledge_id bigint ,
	user_id bigint ,
	picture text,
	title text,
	subtitle text,
	description text,
	createtime timestamp,
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create or replace function archive_knowledge() returns trigger as $archive_knowledge$
begin
  if (new.version = old.version + 1) then
    insert into knowledge_history (
      knowledge_id,
      user_id,
      picture,
      title,
      subtitle,
      description,
      createtime,
      status,
      proc_name,
      proc_channel,
      proc_ts,
      proc_user_id,
      session_id,
      version
    ) values (
      old.knowledge_id,
      old.user_id,
      old.picture,
      old.title,
      old.subtitle,
      old.description,
      old.createtime,
      old.status,
      old.proc_name,
      old.proc_channel,
      old.proc_ts,
      old.proc_user_id,
      old.session_id,
      old.version
    );
    return new;
  else
    raise exception 'update locked.';
  end if;
end;
$archive_knowledge$ language plpgsql;
create trigger archive_knowledge before update on knowledge for each row execute procedure archive_knowledge();


/*user_albums*/

create table user_albums (
	album_id bigserial primary key,
	user_id bigint references users on update cascade on delete restrict,
	original_photo text,
	compressed_photo text,
	remark text,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index user_albums_user_id on user_albums using BTREE (user_id);

create table user_albums_history (
	change_sequence bigserial primary key,
	album_id bigserial,
	user_id bigint,
	original_photo text,
	compressed_photo text,
	remark text,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create or replace function archive_user_albums() returns trigger as $archive_user_albums$
begin
	if (new.version = old.version + 1) then
		insert into user_albums_history (
	    album_id,
	    user_id,
	    original_photo,
	    compressed_photo,
	    remark,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
	    old.album_id,
	    old.user_id,
	    old.original_photo,
	    old.compressed_photo,
	    old.remark,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_user_albums$ language plpgsql;
create trigger archive_user_albums before update on user_albums for each row execute procedure archive_user_albums();



create table activity(
	activity_id bigserial primary key,
	phone text,
	commercial_id bigint references commercials on update cascade on delete restrict,
	address text,
	description text,
	authcode text,
	begin_time timestamp,
	end_time timestamp,
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index activity_phone on activity using BTREE (phone);

create table activity_history(
	change_sequence bigserial primary key,
	activity_id text,
	phone text,
	commercial_id BIGINT,
	address text,
	description text,
	authcode text,
	begin_time timestamp,
	end_time timestamp,
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index activity_history_activity_id on activity_history using BTREE (activity_id);

create or replace function archive_activity() returns trigger as $archive_activity$
begin
	if (new.version = old.version + 1) then
		insert into user_albums_history (
      activity_id,
      phone,
      commercial_id,
      address,
      description,
      authcode,
      begin_time,
      end_time,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
      old.activity_id,
      old.phone,
      old.commercial_id,
      old.address,
      old.description,
      old.authcode,
      old.begin_time,
      old.end_time,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_activity$ language plpgsql;
create trigger archive_activity before update on activity for each row execute procedure archive_activity();



---------------------
-- commercial_evaluates --
---------------------
create table commercial_evaluates (
	commercial_evaluates_id bigserial primary key,
	commercial_id bigint references commercials on update cascade on delete restrict,
	user_id bigint references users on update cascade on delete restrict,
	parent_id BIGINT,
	content text,
	evaluates_time TIMESTAMP,
	status INTEGER,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create index commercial_evaluates_id on commercial_evaluates using BTREE (commercial_evaluates_id);

create table commercial_evaluates_history (
	change_sequence bigserial primary key,
	commercial_evaluates_id BIGINT,
	commercial_id bigint,
	user_id BIGINT,
	parent_id BIGINT,
	content text,
	evaluates_time TIMESTAMP,
	status INTEGER,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create or replace function archive_commercial_evaluates() returns trigger as $archive_commercial_evaluates$
begin
	if (new.version = old.version + 1) then
		insert into commercial_evaluates_history (
      commercial_evaluates_id,
	    commercial_id,
	    user_id,
			parent_id,
	    content,
	    evaluates_time,
			status,
	    proc_name,
	    proc_channel,
	    proc_ts,
	    proc_user_id,
	    session_id,
	    version
		) values (
			old.commercial_evaluates_id,
	    old.commercial_id,
	    old.user_id,
			old.parent_id,
			old.content,
			old.evaluates_time,
			old.status,
	    old.proc_name,
	    old.proc_channel,
	    old.proc_ts,
	    old.proc_user_id,
	    old.session_id,
	    old.version
		);
		return new;
	else
		raise exception 'update locked.';
	end if;
end;
$archive_commercial_evaluates$ language plpgsql;
create trigger archive_commercial_evaluates before update on commercial_evaluates for each row execute procedure archive_commercial_evaluates();